package de.lizenzverwaltung.model.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import de.lizenzverwaltung.model.dao.LizenzDAO;
import de.lizenzverwaltung.model.dao.ProduktDAO;
import de.lizenzverwaltung.model.dao.ProduktDAOFactory;
import de.lizenzverwaltung.model.entities.Lizenz;
import de.lizenzverwaltung.model.entities.Produkt;

public class LizenzDAOImpl extends DAOBase implements LizenzDAO {

	private Log logger = LogFactory.getLog(LizenzDAOImpl.class);
	private ProduktDAO dao = ProduktDAOFactory.getInstance();

	/**
	 * {@inheritDoc}
	 */
	@Override
	public Lizenz getLizenz(String id) {
		logger.debug("entering getLizenz");
		Lizenz lizenz = null;
		
		//
		String query = "SELECT * FROM lizenzen WHERE lizenzid='"+id+"'";
		try {
			Connection conn = openConnection();
			PreparedStatement pstmt = conn.prepareStatement(query);
			ResultSet rs = pstmt.executeQuery();
			if(rs.next()){
				logger.debug("entering getLizenz - ResultSet");
				//Produkt ermitteln
				Produkt produkt = this.dao.getProdukt(rs.getString("produktid"));
				lizenz = new Lizenz(rs.getString("lizenzId"),produkt,rs.getString("lizenzcode"),rs.getInt("lizenzanzahl"));		
			}
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			logger.error("SQLException occured (Query=" + query + ")", e);
		}
		return lizenz;
	}
	
	/**
	 * {@inheritDoc}
	 */
	@Override
	public List<Lizenz> getAllLicences() {
		logger.debug("entering getAllLicences");
		List<Lizenz> lizenzListe = new ArrayList<Lizenz>();
		
		//
		String query = "SELECT * FROM lizenzen";
		try {
			Connection conn = openConnection();
			PreparedStatement pstmt = conn.prepareStatement(query);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()){
				logger.debug("entering getAllLicences - ResultSet");
				//Produkt ermitteln
				Produkt produkt = this.dao.getProdukt(rs.getString("produktid"));
				lizenzListe.add(new Lizenz(rs.getString("lizenzId"),produkt,rs.getString("lizenzcode"),rs.getInt("lizenzanzahl")));		
			}
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			logger.error("SQLException occured (Query=" + query + ")", e);
		}
		return lizenzListe;
	}

	/**
	 * {@inheritDoc}
	 */
	@Override
	public int getID(String name) {
		logger.debug("entering getID");
		int id = -1;
		
		//
		String query = "SELECT * FROM lizenzen WHERE lizenzcode='"+name+"'";
		try {
			Connection conn = openConnection();
			PreparedStatement pstmt = conn.prepareStatement(query);
			ResultSet rs = pstmt.executeQuery();
			if(rs.next()){
				logger.debug("entering getID - ResultSet");
				id = rs.getInt("lizenzId");
				
			}
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			logger.error("SQLException occured (Query=" + query + ")", e);
		}
		return id;
	}
	
	/**
	 * {@inheritDoc}
	 */
	@Override
	public boolean speicherLizenz(String produktname, String lizenzcode, int anzahl) {
		logger.debug("entering speicherLizenz");
		boolean result = false ;
		String query = "INSERT INTO lizenzen(produktid,lizenzcode,lizenzanzahl) VALUES(?,?,?)";
		try {
			Connection conn = openConnection() ;
			PreparedStatement pstmt = conn.prepareStatement(query) ;
			pstmt.setString(1, ""+this.dao.getID(produktname)) ;
			pstmt.setString(2, lizenzcode) ;
			pstmt.setInt(3, anzahl) ;
			pstmt.execute() ;
			pstmt.close();
			conn.close(); 
			result = true ;
		} catch (SQLException sqle) {
			logger.error("SQLException occured (Query=" + query + ")", sqle) ;
			result = false ;
		}
		return result;
	}

	/**
	 * {@inheritDoc}
	 */
	@Override
	public boolean loescheLizenz(String id) {
		logger.debug("entering loescheLizenz");
		boolean result = false ;
		
		String query = "DELETE FROM lizenzen where lizenzid='"+id+"'";
		try {
			Connection conn = openConnection() ;
			PreparedStatement pstmt = conn.prepareStatement(query) ;
			pstmt.execute() ;
			pstmt.close();
			conn.close(); 
			result = true ;
		} catch (SQLException sqle) {
			logger.error("SQLException occured (Query=" + query + ")", sqle) ;
			result = false ;
		}
		return result;
	}

	/**
	 * {@inheritDoc}
	 */
	@Override
	public Lizenz lizenzZuProdukt(String produktname) {
		logger.debug("entering lizenzenZuProdukt");
		Lizenz lizenz = null;
		String query = "SELECT * FROM lizenzen WHERE produktID = ?";
		try {
			Connection conn = openConnection();
			PreparedStatement pstmt = conn.prepareStatement(query);
			pstmt.setString(1, ""+this.dao.getID(produktname));
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()){
				logger.debug("entering lizenzenZuProdukt - ResultSet");
				Produkt produkt = this.dao.getProdukt(rs.getString("produktid"));
				lizenz = new Lizenz(rs.getString("lizenzid"), produkt, rs.getString("lizenzcode"), rs.getInt("lizenzanzahl"));	
			}
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			logger.error("SQLException occured (Query=" + query + ")", e);
		}
		return lizenz;
	}
	
	/**
	 * {@inheritDoc}
	 */
	@Override
	public boolean aendernLizenz(String id, String code, int anzahl) {
		logger.debug("entering aendernLizenz");
		boolean result = false ;
		
		String query = "UPDATE lizenzen SET lizenzcode=?,lizenzanzahl=? WHERE lizenzID = ?";
		try {
			Connection conn = openConnection() ;
			PreparedStatement pstmt = conn.prepareStatement(query) ;
			pstmt.setString(1, code);
			pstmt.setInt(2, anzahl);
			pstmt.setString(3, id);
			pstmt.execute() ;
			pstmt.close();
			conn.close(); 
			result = true ;
		} catch (SQLException sqle) {
			logger.error("SQLException occured (Query=" + query + ")", sqle) ;
			result = false ;
		}
		return result;
	}

	

}
